﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;         
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DAL;
//using MySql.Data;
using System.Data.Odbc;
//using MySql.Data.MySqlClient;
using System.Data.SqlClient;

namespace KPIS.GERP.GBMS.DAL
{
    public sealed class ProjectJobAssigneeListDAL : IProjectJobAssigneeListDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        //private MySqlConnection conn = new MySqlConnection(SystemInfo._ConnectionString);
        private SqlConnection conn = new SqlConnection(SystemInfo._ConnectionString);


        public DataTable Search(string strProjJobAssigneeSeq, string strProjJobAssignSeq, string strEmpSeq, string strRemark)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@bgm_proj_job_assignee_seq", strProjJobAssigneeSeq);
                dbManager.AddParameters(1, "@bgm_proj_job_assign_seq", strProjJobAssignSeq);
                dbManager.AddParameters(2, "@status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(3, "@emp_seq", strEmpSeq);
                dbManager.AddParameters(4, "@job_remark", strRemark);

                strSQL = "SELECT CONCAT(np.prefix_name,emp.first_name,emp.last_name) AS emp_name"
                         + ",bgm_proj_job_assign_seq, bgm_proj_job_assignee_seq, p.pos_name, js.job_site_name"
                         + ", ass.job_remark ,ass.emp_seq"
                         +" FROM bgm_project_job_assignee ass"
                         +" LEFT JOIN hrm_ps_employees emp"
                         +" ON ass.emp_seq = emp.emp_seq"
                         +" INNER JOIN mas_name_prefixes np"
                         +" ON emp.name_prefix_seq = np.name_prefix_seq"
                         +" LEFT JOIN hrm_mp_positions p"
                         + " ON p.pos_seq = emp.pos_seq"
                         +" LEFT JOIN hrm_mp_job_sites js"
                         +" ON emp.working_job_site_seq = js.job_site_seq"
                         + " WHERE ass.record_status not like @status"
                         + (!string.IsNullOrEmpty(strProjJobAssigneeSeq) ? " and bgm_proj_job_assignee_seq = @bgm_proj_job_assignee_seq" : "")
                    + (!string.IsNullOrEmpty(strProjJobAssignSeq) ? " and bgm_proj_job_assign_seq = @bgm_proj_job_assign_seq" : "")
                    + (!string.IsNullOrEmpty(strEmpSeq) ? " and emp_seq = @emp_seq" : "")
                    + (!string.IsNullOrEmpty(strRemark) ? " and job_remark = @job_remark" : "")
                          + " ORDER BY emp_name, p.pos_name, js.job_site_name";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb;

            }
            catch(Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void Insert(string strProjJobAssigneeSeq, string strEmpSeq, string strRemark)
        {
            string strSQL;

            try                    
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@bgm_proj_job_assign_seq", strProjJobAssigneeSeq);
                dbManager.AddParameters(1, "@emp_seq", strEmpSeq);
                dbManager.AddParameters(2, "@job_remark", strRemark);
                dbManager.AddParameters(3, "@created_by", strEmpSeq);
                dbManager.AddParameters(4, "@created_when", DateTime.Now);
                dbManager.AddParameters(5, "@record_status", SystemInfo._ActiveRecordStatus);     

                strSQL = @"INSERT INTO bgm_project_job_assignee
                            (bgm_proj_job_assign_seq, emp_seq, job_remark, record_status, created_by, created_when)
                          VALUES (@bgm_proj_job_assign_seq, @emp_seq, @job_remark, @record_status, @created_by, @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public DataTable LoadEmployeeList(string strProjJobAssigneeSeq, string strEmpSeq, string strJobSite, string strPosName)
        {
//            string strSQL;
//            DataTable dtb = null;

//            try                    
//            {
//                dbManager.Open();
//                dbManager.CreateParameters(3);

//                dbManager.AddParameters(0, "@job_site_seq", strJobSite);
//                dbManager.AddParameters(1, "@emp_name", strEmpSeq);
//                dbManager.AddParameters(2, "@bgm_proj_job_assignee_seq", strProjJobAssigneeSeq);

//                strSQL = @"SELECT emp.emp_seq,
//                            CONCAT(n.prefix_name, emp.first_name, ' ',emp.last_name) AS emp_name,
//							p.pos_name, js.job_site_seq
//                            FROM hrm_ps_employees emp
//                            LEFT JOIN hrm_ps_employee_statuses es
//                            ON emp.emp_status_seq = es.emp_status_seq
//                            LEFT JOIN hrm_mp_job_sites js
//                            ON emp.working_job_site_seq = js.job_site_seq
//                            INNER JOIN mas_name_prefixes n
//                            ON emp.name_prefix_seq = n.name_prefix_seq																										
//                            INNER JOIN hrm_mp_positions p
//                            ON emp.pos_seq = p.pos_seq
//                            WHERE es.active = '1'"
//                    + (!string.IsNullOrEmpty(strJobSite) ? " and emp.working_job_site_seq = @job_site_seq" : "");
                           
//                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            try
            {

                conn.Open();
                //MySqlCommand cmd = new MySqlCommand();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "bgm_project_job_assignment_sel";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@strJobSite", strJobSite);
                cmd.Parameters.AddWithValue("@vharRecordStatus", SystemInfo._DeleteRecordStatus);

                //MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dtb = new DataTable();
                da.Fill(dtb);

                return dtb;

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                 //dbManager.Dispose();
                conn.Close();
            }
            //return dtb;
        }


        public void Update(string strProjJobAssigneeSeq, string strEmpSeq, string strRemark)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@emp_seq", strEmpSeq);
                dbManager.AddParameters(1, "@job_remark", strRemark);
                dbManager.AddParameters(2, "@bgm_proj_job_assignee_seq", strProjJobAssigneeSeq);
                dbManager.AddParameters(3, "@updated_by", strEmpSeq);
                dbManager.AddParameters(4, "@updated_when", DateTime.Now);

                strSQL = @"UPDATE bgm_project_job_assignee
                         SET emp_seq = @emp_seq
                            , job_remark = @job_remark
                            , updated_by = @updated_by
                            , updated_when = @updated_when
                         WHERE bgm_proj_job_assignee_seq = @bgm_proj_job_assignee_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(string strProjJobAssigneeSeq)
        {
            string strSQL;

            try
            {
                dbManager.Open();

                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", strProjJobAssigneeSeq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = @"UPDATE bgm_project_job_assignee 
                          SET record_status = @status 
                          WHERE bgm_proj_job_assignee_seq = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public bool CheckDuplicateCode(string strEmpSeq, string strProjJobAssignSeq)
        {
            string strSQL;

            bool result = false;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@emp_seq", strEmpSeq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@bgm_proj_job_assign_seq", strProjJobAssignSeq);

                strSQL = @"SELECT emp_seq FROM bgm_project_job_assignee
                            WHERE emp_seq = @emp_seq AND bgm_proj_job_assign_seq = @bgm_proj_job_assign_seq
                            AND record_status not like @status";

                DataTable dtb = dbManager.ExecuteDataTable(CommandType.Text, strSQL);

                foreach (DataRow dr in dtb.Rows)
                {
                    result = true;
                    break;
                }
                //while (rdr.Read())
                //{
                //    result = true;
                //    break;
                //}
            }
            catch (Exception error)
            {
                throw error;
            }
            finally 
            {
                dbManager.CloseReader();
                dbManager.Dispose();
             }

                return result;
        }
        
     }
    }
